<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.util.List" %>
<%@ page import="cn.edu.scau.cmi.BookBean"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>显示图书列表</title>
<style type="text/css">td,th{ padding:5px;}</style>
</head>
<body>
<%
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jsp";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url,username,password);
Statement stmt = conn.createStatement();
String sql= "select*from tb_book";
ResultSet rs= stmt.executeQuery(sql);
List<BookBean> list = new ArrayList<>();
while(rs.next()){
	BookBean book = new BookBean();
	book.setId(rs.getInt("id"));
	book.setName(rs.getString("name"));
	book.setPrice(rs.getDouble("price"));
	book.setBookCount(rs.getInt("bookCount"));
	book.setAuthor(rs.getString("author"));
    list.add(book);
}
request.setAttribute("list",list);
%>
<table width="98%" border="0" align="center" cellpadding="0"
cellspacing="1" bgcolor="#666666">
<tr>
<th bgcolor="#FFFFFF">ID</th>
<th bgcolor="#FFFFFF">图书名称</th>
<th bgcolor="#FFFFFF">价格</th>
<th bgcolor="#FFFFFF">数量</th>
<th bgcolor="#FFFFFF">作者</th>
</tr>
<%

if (list == null ||list.size()<1){
  out.print("<tr><td bgcolor='#FFFFFF' colspan='5'>没有图书信息</td></tr>");
} else {
for(BookBean book : list){
%>
<tr align="center">
<td bgcolor="#FFFFFF" ><%=book.getId()%></td> 
<td bgcolor="#FFFFFF" ><%=book.getName()%></td> 
<td bgcolor="#FFFFFF" ><%=book.getPrice()%></td> 
<td bgcolor="#FFFFFF" ><%=book.getBookCount()%></td>
<td bgcolor="#FFFFFF" ><%=book.getAuthor()%></td>
</tr>
<%}
}
%>
</table>  

</body>
</html>